package com.boot.unity.api.sql.upgrade.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface SqlDao {

    /*@Select("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = 'whjpk'")
    List<String> showAllVeiws();


    @Insert("create view v_sex as select '1' AS `sex`,'男' AS `sex_name` union select '2' AS `sex`,'女' AS `sex_name`")
    public int c_view_v_sex();


    @Insert("create view v_xying as " +
            "select `a`.`school_id` AS `school_id`,`a`.`num` AS `ing`,ifnull((select `b`.`num` from `v_xying_no` `b` where (`b`.`school_id` = `a`.`school_id`)),0) AS `no`,ifnull((select `c`.`num` from `v_xying_full` `c` where (`c`.`school_id` = `a`.`school_id`)),0) AS `full` from `v_xying_ing` `a`")
    public int c_view_v_xying();


    @Insert("create view v_full_today_time as " +
            "select a.* from `province_training_time` `a`,`v_train_learntime_setting` `b` where ((`a`.`pxcx` = `b`.`train_type`) and (`a`.`pxkm` = `b`.`subject`) and (`b`.`pxxs` >= `b`.`pxxs`) and (`a`.`createTime` like CONCAT(DATE_FORMAT(now(),'%Y-%m-%d'),'%')))")
    public int c_view_v_full_today_time();

    @Insert("create view v_pay_province_training_time as " +
            "select `a`.`id` AS `id`,`a`.`sfzmhm` AS `sfzmhm`,`a`.`pxkm` AS `pxkm`,`a`.`pxxs` AS `pxxs`,`a`.`pxjl` AS `pxjl`,`a`.`jly` AS `jly`,`a`.`shdwmc` AS `shdwmc`,`a`.`checker` AS `checker`,`a`.`shrq` AS `shrq`,`a`.`fzjg` AS `fzjg`,`a`.`pxcx` AS `pxcx`,`a`.`origin` AS `origin`,`a`.`jszt` AS `jszt`,`a`.`createTime` AS `createTime`,`a`.`updateTime` AS `updateTime`,`a`.`remark` AS `remark` from (`province_training_time` `a` join `v_train_learntime_setting` `b`) where ((`a`.`origin` = 1) and (`a`.`jszt` = 0) and (`a`.`pxcx` = `b`.`train_type`) and (`a`.`pxkm` = `b`.`subject`) and (`a`.`pxxs` >= `b`.`pxxs`))")
    public int c_view_v_pay_province_training_time();*/


    @Update("update train_coach set pic = REPLACE(pic,'https://tp.jpk.weihai.cn','https://whjpk.sdrcu.com:9000/img')," +
            "qrurl = CONCAT('https://whjpk.sdrcu.com:9000/img/images/',id,idcard,'.png')," +
            "zsurl = CONCAT('https://whjpk.sdrcu.com:9000/img/images/',id,idcard,'hb.jpg')")
    int updateCoachPic();


    @Update("update train_manager set " +
            "qrurl = CONCAT('https://whjpk.sdrcu.com:9000/img/images/',id,idcard,'.png')," +
            "zsurl = CONCAT('https://whjpk.sdrcu.com:9000/img/images/',id,idcard,'hb.jpg')")
    int updateManagerPic();


    @Update("update litemall_ad set url = replace(url,'https://tp.jpk.weihai.cn/img/','https://whjpk.sdrcu.com:9000/img/img/')")
    int updateAd();

    @Update("update litemall_dept set cover_pic=REPLACE(cover_pic,'https://tp.jpk.weihai.cn','https://whjpk.sdrcu.com/img')," +
            "main_pic=REPLACE(main_pic,'https://tp.jpk.weihai.cn','https://whjpk.sdrcu.com/img')")
    int updateDeptImg();
}
